﻿using aier.dataaccess.model;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    /// <summary>
    /// 筛查资源dal
    /// </summary>
    public class EquipmentDal
    {
        public DapperHelper dapperHelper { get; set; }

        /// <summary>
        /// 获取全部筛查资源
        /// </summary>
        /// <returns>筛查资源列表</returns>
        public IEnumerable<EquipmentView> GetAllEquipments() {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<EquipmentView>>(a =>
            {
                string sql = @"select new_name ,new_equipmentid,new_number  from new_equipmentbase as c order by new_number desc";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.Query<EquipmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据筛查信息id查询对应的筛选资源列表
        /// </summary>
        /// <param name="screenId">筛查信息id</param>
        /// <returns></returns>
        public IEnumerable<EquipmentView> GetEquipmentsByScreenId(Guid screenId) {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<EquipmentView>>(a =>
            {
                string sql = @"select se.new_equipmentid ,e.new_name   from new_new_screen_new_equipment as se
                                 /**leftjoin**/  /**where**/ ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.LeftJoin("new_equipment as e on se.new_equipmentid = e.new_equipmentId");
                sqlBuilder.Where("se.new_screenid = @screenId", new { screenId = screenId });
                return a.Query<EquipmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 营销资源设备查询
        /// 原程序：ScreeningServiceImpl-> listEquipment(String name, Integer type, Integer statecode)
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="type">资源分类</param>
        /// <param name="statecode">状态</param>
        /// <returns></returns>
        public IEnumerable<EquipmentView> QueryEquipmentViewByNameAndType(string name, int type, int statecode)
        {
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters sqlParams = new DynamicParameters();

            return dapperHelper.ExecueWithoutTransaction<IEnumerable<EquipmentView>>(a =>
            {
                string sql = $@"select 
                                CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, new_equipmentId, 
                                OrganizationId, statecode,  new_name, new_type, new_number, 
                                new_specification, new_unit, new_price, TransactionCurrencyId, ExchangeRate, new_price_Base, 
                                new_point, new_salesliterature from new_equipment /**where**/  ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where(" @new_type=new_type ", new { new_type = type });
                sqlBuilder.Where(" @statecode=statecode ", new { statecode = statecode });

                if (!string.IsNullOrEmpty(name))
                {
                    sqlBuilder.Where(" charindex(@new_name,new_name)>0 ", new { new_name = name });
                }

                return a.Query<EquipmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }

        /// <summary>
        /// 根据设备ID查询，id间用','号分开
        /// 原程序：ScreenInfoServiceImpl-> qryNewEquipmentsByEqids(String eqids)
        /// </summary>
        /// <param name="eqids"></param>
        /// <returns></returns>
        public IEnumerable<EquipmentView> QueryEquipmentViewById(string eqids)
        {
            var ids = eqids.Split(new char[] { ',' });
            SqlBuilder sqlBuilder = new SqlBuilder();
            DynamicParameters dynamicParameters = new DynamicParameters();
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<EquipmentView>>(a =>
            {
                string sql = $@"select
                                new_equipmentId,CreatedOn, CreatedBy, ModifiedOn, ModifiedBy, 
                                OrganizationId, statecode, new_name, new_type, new_number, 
                                new_specification, new_unit, new_price, TransactionCurrencyId, ExchangeRate, new_price_Base, 
                                new_point, new_salesliterature from new_equipment /**where**/  ";
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where(" new_equipmentId in @new_equipmentId ");
                dynamicParameters.Add("@new_equipmentId", ids);

                return a.Query<EquipmentView>(sqlTemplate.RawSql, dynamicParameters);
            });
        }

        /// <summary>
        /// 根据筛查ID,查询营销资源设备
        /// 原程序 ：ScreenInfoServiceImpl-> qryEquipmentIdsByScreenid(String screenid, DoctorUser doctorUser)
        /// </summary>
        /// <param name="screenId">筛查ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenEquipmentView> QueryScreenEquipmentViewByScreenId(string screenId)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenEquipmentView>>(a =>
            {
                string sql = $@"select * from new_new_screen_new_equipment where new_screenid='{screenId}'  ";
                var sqlTemplate = new SqlBuilder().AddTemplate(sql);

                return a.Query<ScreenEquipmentView>(sqlTemplate.RawSql);
            });
        }

        /// <summary>
        /// 根据筛查ID,查询营销资源设备
        /// 原程序 ：ScreenInfoServiceImpl-> qryEquipmentIdsByScreenid(String screenid, DoctorUser doctorUser)
        /// </summary>
        /// <param name="screenId">筛查ID</param>
        /// <returns></returns>
        public IEnumerable<ScreenEquipmentView> QueryCurrentId(string[] currentNumber)
        {
            return dapperHelper.ExecueWithoutTransaction<IEnumerable<ScreenEquipmentView>>(select =>
            {
                string sql = @"select new_equipmentId from new_equipment /**where**/ ";
                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                sqlBuilder.Where("new_number in @currentNumber", new { currentNumber = currentNumber });
                return select.Query<ScreenEquipmentView>(sqlTemplate.RawSql, sqlTemplate.Parameters);
            });
        }
    }
}
